package com.ckv.qcm.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ckv.qcm.model.Answer;
import com.ckv.qcm.model.AnswerType;
import com.ckv.qcm.model.Question;
import com.ckv.qcm.model.Theme;

public class QuestionDAO extends DAO
{


	public Question find(long id)
	{
		Question question = new Question();
		ArrayList<Answer> answers = null;
		ArrayList<Theme> themes = new ArrayList<Theme>();

		try
		{
			ResultSet result = this.connection
					.createStatement()
					.executeQuery(
							"SELECT Questions.idQuestion, Questions.Libelle, Questions.Enonce, Questions.TypeReponse, Questions.Image FROM Questions WHERE Questions.idQuestion = "
									+ id);

			if (result.next())
			{

				question.setId(result.getLong("idQuestion"));
				question.setDescription(result.getString("Libelle"));
				question.setStatement(result.getString("Enonce"));
				question.setAnswerType(AnswerType.values()[result
						.getInt("TypeReponse")]);
				question.setImage(result.getString("Image"));

				answers = new ArrayList<Answer>(Answer.findByQuestion(id));

				result = connection
						.createStatement()
						.executeQuery(
								"SELECT Themes.idTheme FROM Questions LEFT OUTER JOIN Rel_theme_Question ON Questions.idQuestion = Rel_theme_Question.idQuestion LEFT OUTER JOIN Themes ON Rel_theme_Question.idTheme = Themes.idTheme WHERE Questions.idQuestion = "
										+ id);

				while (result.next())
				{
					themes.add(Theme.find(result.getLong("idTheme")));
				}

			}

			question.setAnswers(answers);
			question.setThemes(themes);

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return question;
	}

	public ArrayList<Question> findAll()
	{
		ArrayList<Question> questions = new ArrayList<Question>();

		try
		{
			ResultSet result = this.connection.createStatement().executeQuery(
					"SELECT idQuestion FROM Questions");

			while (result.next())
				questions.add(this.find(result.getLong("idQuestion")));

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return questions;
	}

	public Question create(Question question)
	{
		ResultSet generatedKeys = null;
		long idQuestion = 0;
		try
		{
			PreparedStatement preparedStatement = this.connection
					.prepareStatement(
							"INSERT INTO Questions (Libelle, Enonce, TypeReponse, Image) VALUES (?, ? ,? ,?)",
							Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, question.getDescription());
			preparedStatement.setString(2, question.getStatement());
			preparedStatement.setString(3, question.getAnswerType().toString());
			preparedStatement.setString(4, question.getImage());

			int affectedRows = preparedStatement.executeUpdate();
			if (affectedRows == 0)
			{
				throw new SQLException(
						"Creating answer failed, no rows affected.");
			}

			generatedKeys = preparedStatement.getGeneratedKeys();
			if (generatedKeys.next())
			{
				idQuestion = generatedKeys.getLong(1);
			}
			else
			{
				throw new SQLException(
						"Creating answer failed, no generated key obtained.");
			}

			for (Answer answer : question.getAnswers())
			{
				answer.setIdQuestion(idQuestion);
				answer.create();
			}

			for (Theme theme : question.getThemes())
			{

				if (theme.getId() == 0)
				{
					theme = theme.create();
				}

				preparedStatement = this.connection
						.prepareStatement("INSERT INTO Rel_Theme_Question (idTheme, idQuestion) VALUES (?, ?)");
				preparedStatement.setLong(1, theme.getId());
				preparedStatement.setLong(2, idQuestion);

				preparedStatement.execute();
			}

			question = this.find(idQuestion);

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return question;
	}

	public Question update(Question question)
	{
		try
		{
			PreparedStatement preparedStatement = this.connection
					.prepareStatement("UPDATE Questions SET Libelle = '"
							+ question.getDescription() + "', Enonce = '"
							+ question.getStatement() + "', TypeReponse = '"
							+ question.getAnswerType() + "', Image = '"
							+ question.getImage() + "'");

			preparedStatement.executeUpdate();

			for (Answer answer : question.getAnswers())
			{
				answer.update();
			}

			for (Theme theme : question.getThemes())
			{
				theme.update();
			}

			question = this.find(question.getId());

		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}

	public boolean delete(Question question)
	{
		try
		{
			for (Answer answer : question.getAnswers())
			{
				answer.delete();
			}
			this.connection.createStatement().executeUpdate(
					"DELETE FROM Questions WHERE idQuestion = "
							+ question.getId());
			return true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return false;
	}

}
